﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace TMS
{
    class Product
    {
        private int ID;
        private int Category_1;
        private int Category_2;
        private int Category_3;
        private string Name;
        private string Part_Num;
        private double Nominal_Price;
        private int Type;
        private string TypeName;
        private int Track;
        private int Min_Quantity;
        private string Description;
        private string Measure;
       
        public Product() { 
        
        }

        //sluzi za brisenje
        public Product(int ID)
        {
            this.ID = ID;
        }

        //za proverka na postoecki produkt vo baza
        public Product(string Name, string Part_Num)
        {
            this.Name = Name;
            this.Part_Num = Part_Num;
        }

        public Product(int ID, int Cat_1, int Cat_2, int Cat_3, string Name, string Part_Num, double Price, int Type, int Track, int Min_Quantity, string Desc, string Measure)
        {
            this.ID = ID;
            this.Category_1 = Cat_1;
            this.Category_2 = Cat_2;
            this.Category_3 = Cat_3;
            this.Name = Name;
            this.Part_Num = Part_Num;
            this.Nominal_Price = Price;
            this.Type = Type;
            this.TypeName = (Type == 1) ? "Service" : "Product";
            this.Track = Track;
            this.Min_Quantity = Min_Quantity;
            this.Description = Desc;
            this.Measure = Measure;
        }

        public DataSet GetProductFromDB(string cat1_ID, string cat2_ID, string cat3_ID)
        {
            DataSet result = new DataSet();
            if (cat1_ID == "1") cat1_ID = "%";
            if (cat2_ID == "1") cat2_ID = "%";
            if (cat3_ID == "1") cat3_ID = "%";

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT Name,ID FROM products WHERE Category_1 LIKE '" + cat1_ID + "' AND Category_2 LIKE '" + cat2_ID + "' AND Category_3 LIKE '" + cat3_ID+"'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
                  

            MyDA.Fill(result);

            mysqlCon.Close();

            return result;
        }

       
         public DataSet GetCompleateProductFromDB(string cat1_ID, string cat2_ID, string cat3_ID)
        {
            DataSet result = new DataSet();
            if (cat1_ID == "1") cat1_ID = "%";
            if (cat2_ID == "1") cat2_ID = "%";
            if (cat3_ID == "1") cat3_ID = "%";

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT * FROM products WHERE Category_1 LIKE '" + cat1_ID + "' AND Category_2 LIKE '" + cat2_ID + "' AND Category_3 LIKE '" + cat3_ID + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);


            MyDA.Fill(result);

            mysqlCon.Close();

            return result;
        }

         public bool IsInCategory(string cat1_ID, string cat2_ID, string cat3_ID, string ProductName)
         {
             DataSet result = new DataSet();
             if (cat1_ID == "1") cat1_ID = "%";
             if (cat2_ID == "1") cat2_ID = "%";
             if (cat3_ID == "1") cat3_ID = "%";

             string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
             MySqlDataAdapter MyDA = new MySqlDataAdapter();

             MySqlConnection mysqlCon = new MySqlConnection(connString);
             mysqlCon.Open();


             string sqlSelectAll = "SELECT * FROM products WHERE Category_1 LIKE '" + cat1_ID + "' AND Category_2 LIKE '" + cat2_ID + "' AND Category_3 LIKE '" + cat3_ID + "' AND Name LIKE '" + ProductName + "'";
             MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);


             MyDA.Fill(result);

             mysqlCon.Close();
             if (result.Tables[0].Rows.Count > 0) return true;
             else return false;

         }



         public Int32 GetProductMinimalQuantity(int prodID)
         {

             MySqlDataAdapter MyDA = new MySqlDataAdapter();
             string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

             MySqlConnection mysqlCon = new MySqlConnection(connString);

             mysqlCon.Open();

             string sqlSelectAll = "SELECT Min_quantity FROM products WHERE ID='" + prodID + "'";

             MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
             DataSet result = new DataSet();
             MyDA.Fill(result);
             mysqlCon.Close();
             return Convert.ToInt32(result.Tables[0].Rows[0].ItemArray[0]);

         }

        public DataSet GetProductFromDBAsDataSet(int prodID)
        {

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM products WHERE ID='" + prodID + "'";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result;

        }

        public void InsertProductInDB() {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();


            string sqlInsertProduct = "INSERT INTO products VALUES (DEFAULT," + 
                                        this.Category_1 + "," + 
                                        this.Category_2 + "," + 
                                        this.Category_3 + ",'" + 
                                        this.Name + "','" + 
                                        this.Part_Num + "'," + 
                                        "@Nominal_Price," + 
                                        this.Type + "," + 
                                        this.Track + ", " + 
                                        this.Min_Quantity + ", '" +
                                        this.Description + "', '" +
                                        this.Measure + "')";

            MySqlCommand cmd = new MySqlCommand(sqlInsertProduct, mysqlCon);
            cmd.Parameters.Add("@Nominal_Price", MySqlDbType.Double, 50).Value = this.Nominal_Price;
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public void UpdateProductInDB() {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlUpdateProduct = "UPDATE products " + 
                                      "SET Category_1 ='" + this.Category_1 + "',"+
                                      "Category_2 ='" + this.Category_2 + "', "+
                                      "Category_3 ='" + this.Category_3 + "', " +
                                      "Name ='" + this.Name + "', " +
                                      "Part_Num ='" + this.Part_Num + "', " +
                                      "Nominal_Price = @nominalPrice, " +
                                      "Type = " + this.Type + ", " +
                                      "Trace = " + this.Track + ", " +
                                      "Min_quantity = " + this.Min_Quantity + ", " +
                                      "Description ='" + this.Description + "' ," +
                                      "Measure ='" + this.Measure + "' " +
                                      "WHERE ID = " + this.ID;
            
            
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.Parameters.AddWithValue("@nominalPrice", this.Nominal_Price);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();

        }

        public void DeleteProductFromDB() {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlDeleteProduct = "DELETE FROM products " +
                                      "WHERE ID = " + this.ID;

            MySqlCommand cmd = new MySqlCommand(sqlDeleteProduct, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public bool CheckExistingProductInDB() {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlCheckExistingProduct = "SELECT * FROM products " +
                                             "WHERE Name = '" + this.Name + "' " +
                                             " OR Part_Num = '" + this.Part_Num + "'";

            MySqlCommand cmd = new MySqlCommand(sqlCheckExistingProduct, mysqlCon);
            
            object result = cmd.ExecuteScalar();
            mysqlCon.Close();

            if (result != DBNull.Value && result != null)
            {
                //record exists
                return true;
            }
            else 
            {
                //does not exist
                return false;
            }
        }

        public Int32 GetProductQuantityFromWarehouse(Int32 ProductID)
            {
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();

                string sql = "SELECT SUM(Quantity) FROM warehouse " +
                                             "WHERE Product_ID = '"+ProductID+"'";

                MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            
                object result = cmd.ExecuteScalar();
                mysqlCon.Close();
                if (result != DBNull.Value && result != null)
                {
                    //record exists
                    return Convert.ToInt32(result);
                }
                else
                {
                    //does not exist
                    return 0;
                }
            }

        public DataSet GetAllProductsFromDB()
            {  
                MySqlDataAdapter MyDA = new MySqlDataAdapter();
                DataSet table = new DataSet();
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
                MySqlConnection mysqlCon = new MySqlConnection(connString);
                mysqlCon.Open();
                string sqlSelectAll = "SELECT * from Products ORDER BY Category_1 ASC, Category_2 ASC, Category_3 ASC, Description ASC";
                MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
                MyDA.Fill(table);
                mysqlCon.Close();
                return table;
            }

        public Int32 GetProductQuantityFromWarehouse(Int32 ProductID,string warehouse)
            {
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();

                string sql = "SELECT Quantity FROM warehouse " +
                                             "WHERE Product_ID = '" + ProductID + "' AND Warehouse='"+warehouse+"'";

                MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

                object result = cmd.ExecuteScalar();
                mysqlCon.Close();
                if (result != DBNull.Value && result != null)
                {
                    //record exists
                    return Convert.ToInt32(result);
                }
                else
                {
                    //does not exist
                    return 0;
                }
            }

        public Int32 GetProductQuantity(Int32 ProductID)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT Quantity FROM warehouse " +
                                         "WHERE Product_ID = '" + ProductID +"'";

            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

            object result = cmd.ExecuteScalar();
            mysqlCon.Close();
            if (result != DBNull.Value && result != null)
            {
                //record exists
                return Convert.ToInt32(result);
            }
            else
            {
                //does not exist
                return 0;
            }
        }

        public Int32 GetProductFromStocktaking(Int32 ProductID,Int32 Stocktaking_ID)
            {
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();

                string sql = "SELECT SUM(TakenQuantity) FROM stocktaking_products " +
                                             "WHERE Product_ID = '" + ProductID + "' AND Stocktaking_ID='"+Stocktaking_ID+"'";

                MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

                object result = cmd.ExecuteScalar();
                mysqlCon.Close();
                if (result != DBNull.Value && result != null)
                {
                    //record exists
                    return Convert.ToInt32(result);
                }
                else
                {
                    //does not exist
                    return 0;
                }
            }

        public void ClearWarehouse()
            {
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();

                string sqlUpdateProduct = "UPDATE warehouse SET Quantity ='0'";

                MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
                cmd.ExecuteNonQuery();

                mysqlCon.Close();
            }

        public void UpdateWarehouseQuantity(Int32 ProductID, string warehouse, Int32 Quantity)
            {
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();

                string sql = "SELECT Quantity FROM warehouse " +
                                             "WHERE Product_ID = '" + ProductID + "' AND Warehouse='" + warehouse + "'";

                MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

                object result = cmd.ExecuteScalar();

                if (result == DBNull.Value || result == null)
                {
                    //insert
                    string sqlInsertProduct = "INSERT INTO warehouse VALUES (DEFAULT,'" +
                                        ProductID + "','" +
                                        warehouse + "','" +
                                        Quantity + "',DEFAULT,DEFAULT)";
                    cmd = new MySqlCommand(sqlInsertProduct, mysqlCon);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    //add and update
                    string sqlUpdateProduct = "UPDATE warehouse SET Quantity ='"+(Convert.ToInt32(result)+Quantity)+ "' WHERE Product_ID = '" + ProductID + "' AND Warehouse='" + warehouse + "'";
                   // MessageBox.Show(sqlUpdateProduct);
                    cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
                    cmd.ExecuteNonQuery();
                }
            Product prod = new Product();
            Mail nabavka = new Mail();
            if (prod.GetProductQuantity(ProductID) < prod.GetProductMinimalQuantity(ProductID))
                nabavka.SendForProductOrder(ProductID, prod.GetProductQuantity(ProductID));

                mysqlCon.Close();
            }

        public void UpdateWarehouseQuantityWithInternalDispatc(Int32 ProductID, string warehouse, Int32 Quantity)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT Quantity FROM warehouse " +
                                         "WHERE Product_ID = '" + ProductID + "' AND Warehouse='" + warehouse + "'";

            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

            object result = cmd.ExecuteScalar();

            if (result == DBNull.Value || result == null)
            {
                //insert
                string sqlInsertProduct = "INSERT INTO warehouse VALUES (DEFAULT,'" +
                                    ProductID + "','" +
                                    warehouse + "','" +
                                    Quantity + "',DEFAULT,DEFAULT)";
                cmd = new MySqlCommand(sqlInsertProduct, mysqlCon);
                cmd.ExecuteNonQuery();
            }
            else
            {
                //add and update
                string sqlUpdateProduct = "UPDATE warehouse SET Quantity ='" + (Convert.ToInt32(result) + Quantity) + "' WHERE Product_ID = '" + ProductID + "' AND Warehouse='" + warehouse + "'";
                // MessageBox.Show(sqlUpdateProduct);
                cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
                cmd.ExecuteNonQuery();
            }
            

            mysqlCon.Close();
        }

        
        //INTERNAL DISPATCHES

        public DataSet GetAllInternalDispatches()
            {
                MySqlDataAdapter MyDA = new MySqlDataAdapter();
                DataSet table = new DataSet();
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
                MySqlConnection mysqlCon = new MySqlConnection(connString);
                mysqlCon.Open();
                string sqlSelectAll = "SELECT * from internal_dispatches";
                MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
                MyDA.Fill(table);
                mysqlCon.Close();
                return table;
            }

        public void AddInternalDispatch(Int32 DispatchNo, string Responsible, DateTime Date, string Status, string From, string To)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sqlInsert = "INSERT INTO internal_dispatches VALUES (DEFAULT,'" +
                                        Date.ToString("yyyy-MM-dd") + "','" +
                                        Responsible +"','" +
                                        DispatchNo + "','" +
                                        Status + "','" +
                                        From + "','" +
                                        To+ "')";
            MySqlCommand cmd = new MySqlCommand(sqlInsert, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public Int32 GetLastInternalDispatch()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT MAX(DispatchNo) FROM internal_dispatches";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();
            try
            {
                return Convert.ToInt32(result.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return 0;
            }
        }

        public DataSet GetProductByBarcode(string Barcode)
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM warehouse WHERE Barcode='" + Barcode + "'";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result;
        }
            //A MOZE I VAKA:
            //using (MySqlDataReader existsResult = cmd.ExecuteReader())
            //{

            //    if (existsResult.Read())
            //    {
            //        MessageBox.Show("record exists");
            //    }
            //    else
            //    {
            //        MessageBox.Show("does not exist");
            //    }
            

        
        //RECEIVE NOTES

        public DataSet GetAllReceiveNotes()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            DataSet table = new DataSet();
            
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();
            
            string sqlSelectAll = "SELECT * from receive_notes";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            
            MyDA.Fill(table);
            
            mysqlCon.Close();
            
            return table;
        }

        public DataSet GetAllImortReceiveNotes()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            DataSet table = new DataSet();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT * from receive_notes WHERE Import='1'";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            MyDA.Fill(table);

            mysqlCon.Close();

            return table;
        }

        public void AddReceiveNote(Int32 ReceiveNoteNo, string Responsible, DateTime Date, string Partner, string Status, int Import, string Transport, string Spedition, string Warehouseing, string Valute, string Curse,string PartnerNoteNumber)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            
            mysqlCon.Open();
            
            string sqlInsert = "INSERT INTO receive_notes VALUES (DEFAULT,'" +
                                        Date.ToString("yyyy-MM-dd") + "','" +
                                        Responsible + "','" +
                                        ReceiveNoteNo + "','" +
                                        Partner  + "','" +
                                        Status + "','" +
                                        Import+"',@Transport,@Spedition,@Warehouseing,'"+Valute+"',@Curse,'"+PartnerNoteNumber+"')";
            
            MySqlCommand cmd = new MySqlCommand(sqlInsert, mysqlCon);
            cmd.Parameters.Add("@Transport", MySqlDbType.Double, 50).Value = Transport;
            cmd.Parameters.Add("@Spedition", MySqlDbType.Double, 50).Value = Spedition;
            cmd.Parameters.Add("@Warehouseing", MySqlDbType.Double, 50).Value = Warehouseing;
            cmd.Parameters.Add("@Curse", MySqlDbType.Double, 50).Value = Curse;
            cmd.ExecuteNonQuery();
            
            mysqlCon.Close();
        }

        public Int32 GetLastReceiveNote()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT MAX(Note_Number) FROM receive_notes";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();
            try
            {
                return Convert.ToInt32(result.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return 0;
            }
        }

        public void DeleteAllProductsFromReceiveNoteList(int ReceiveNoteNumber)
        {
            DataSet table = new DataSet();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sql = "DELETE FROM receive_note_products WHERE Note_Number='" + ReceiveNoteNumber + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void DeleteAllProductsFromInternalDispatchNoteList(int ReceiveNoteID)
        {
            DataSet table = new DataSet();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sql = "DELETE FROM internal_dispatch_products WHERE Note_ID='" + ReceiveNoteID + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public DataSet GetAllDispatchNotes()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            DataSet table = new DataSet();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT * from dispatch_note";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            MyDA.Fill(table);

            mysqlCon.Close();

            return table;
        }

        public void AddDispatchNote(Int32 ReceiveNoteNo, string Responsible, DateTime Date, string Partner, string Status)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlInsert = "INSERT INTO dispatch_note VALUES (DEFAULT,'" +
                                        Date.ToString("yyyy-MM-dd") + "','" +
                                        Responsible + "','" +
                                        ReceiveNoteNo + "','" +
                                        Partner + "','" +
                                        Status + "')";

            MySqlCommand cmd = new MySqlCommand(sqlInsert, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public Int32 GetLastDispatchNote()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT MAX(Note_Number) FROM Dispatch_note";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();
            try
            {
                return Convert.ToInt32(result.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return 0;
            }
        }

        public void DeleteAllProductsFromDispatchNoteList(int NoteNumber)
        {
            DataSet table = new DataSet();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sql = "DELETE FROM dispatch_note_products WHERE Note_Number='" + NoteNumber + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void SubstractQuantityFromWarehouse(int ProductID, string warehouse, int quantity)
        {
            UpdateWarehouseQuantity(ProductID, warehouse, (-quantity));
        }

        public Decimal GetAvgPriceFromWarehouse(Int32 ProductID)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT MAX(Averige_Price) FROM warehouse " +
                                         "WHERE Product_ID = '" + ProductID + "'";

            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);

            object result = cmd.ExecuteScalar();
            mysqlCon.Close();
            if (result != DBNull.Value && result != null)
            {
                //record exists
                return Convert.ToDecimal(result);
            }
            else
            {
                //does not exist
                return 0;
            }
        }

        public void UpdateWarehouseAvgPrice(Int32 ProductID, Decimal Price)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();
            string sqlUpdateProduct = "UPDATE warehouse SET Averige_Price =@Price WHERE Product_ID = '" + ProductID + "'";
            MySqlCommand   cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = Price;
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public bool CloseDispatchNote(int NoteNumber)
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT dispatch_note_products.*,warehouse.Averige_Price,warehouse.Quantity from dispatch_note_products LEFT JOIN warehouse ON warehouse.Product_ID=dispatch_note_products.ProductID AND warehouse.Warehouse=dispatch_note_products.Warehouse  WHERE dispatch_note_products.Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);

            foreach (DataRow row in table.Tables[0].Rows)
            {
                try
                {
                    if (Convert.ToInt32(row.ItemArray[2]) > Convert.ToInt32(row.ItemArray[8]))
                    {
                        MessageBox.Show("Проверете ja Испратницата\nИстата содржи производи кои ги нема доволно во магацин");
                        return false;
                    }
                }
                catch
                {
                    MessageBox.Show("Проверете ja Испратницата\nИстата содржи производи кои ги нема доволно во магацин");
                    return false;
                }
            }


            foreach (DataRow row in table.Tables[0].Rows)
            {
                //SubstractQuantityFromWarehouse(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), Convert.ToInt32(row.ItemArray[2]));
                UpdateWarehouseQuantity(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), (-Convert.ToInt32(row.ItemArray[2])));
                string sqlUpdatePrice = "UPDATE dispatch_note_products SET Price =@Price WHERE ID='" + Convert.ToInt32(row.ItemArray[0]) + "'";
                MySqlCommand cmdU = new MySqlCommand(sqlUpdatePrice, mysqlCon);
                cmdU.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = row.ItemArray[7];
                cmdU.ExecuteNonQuery();
            }

            string sqlUpdateProduct = "UPDATE dispatch_note SET Status ='Затворена' WHERE Note_Number='"+NoteNumber+"'";
            // MessageBox.Show(sqlUpdateProduct);
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
            return true;
        }

        public bool CloseInternalDispatchNote(int NoteNumber)
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT internal_dispatch_products.*,warehouse.Quantity from internal_dispatch_products LEFT JOIN warehouse ON warehouse.Warehouse=internal_dispatch_products.FromWarehouse AND warehouse.Product_ID=internal_dispatch_products.ProductID WHERE Note_ID='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);

            foreach (DataRow row in table.Tables[0].Rows)
            {
                try
                {
                    if (Convert.ToInt32(row.ItemArray[2]) > Convert.ToInt32(row.ItemArray[7]))
                    {
                        MessageBox.Show("Проверете ja Испратницата\nИстата содржи производи кои ги нема доволно во излезниот магацин");
                        return false;
                    }
                }
                catch
                {
                    MessageBox.Show("Проверете ja Испратницата\nИстата содржи производи кои ги нема доволно во излезниот магацин");
                    return false;
                }
            }


            foreach (DataRow row in table.Tables[0].Rows)
            {
                //SubstractQuantityFromWarehouse(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), Convert.ToInt32(row.ItemArray[2]));
                UpdateWarehouseQuantityWithInternalDispatc(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), (-Convert.ToInt32(row.ItemArray[2])));
                UpdateWarehouseQuantityWithInternalDispatc(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[6].ToString(), (Convert.ToInt32(row.ItemArray[2])));
                UpdateWarehouseAvgPrice(Convert.ToInt32(row.ItemArray[1]),GetAvgPriceFromWarehouse(Convert.ToInt32(row.ItemArray[1])));
            }

            string sqlUpdateProduct = "UPDATE internal_dispatches SET Status ='Затворена' WHERE DispatchNo='" + NoteNumber + "'";
            // MessageBox.Show(sqlUpdateProduct);
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
            return true;
        }

        public void CloseReceiveNote(int NoteNumber)
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT receive_note_products.*,receive_notes.InvoiceNo from receive_note_products LEFT JOIN receive_notes ON receive_notes.Note_Number=receive_note_products.Note_Number WHERE receive_note_products.Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);


            foreach (DataRow row in table.Tables[0].Rows)
            {
                Int32 OldQuantity=GetProductQuantityFromWarehouse(Convert.ToInt32(row.ItemArray[1]));
                Int32 NewQuantity= Convert.ToInt32(row.ItemArray[2]);
                Decimal OldPrice=GetAvgPriceFromWarehouse(Convert.ToInt32(row.ItemArray[1]));
                Decimal NewPrice=Convert.ToDecimal(row.ItemArray[6]);
                UpdateWarehouseQuantity(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), NewQuantity);
                if (row.ItemArray[9].ToString() != "Повратница")
                    if (OldPrice == 0)
                        UpdateWarehouseAvgPrice(Convert.ToInt32(row.ItemArray[1]), NewPrice);
                    else
                        UpdateWarehouseAvgPrice(Convert.ToInt32(row.ItemArray[1]), (OldPrice * OldQuantity + NewPrice * NewQuantity) / (OldQuantity + NewQuantity));
                string AvePrice = "UPDATE receive_note_products SET AveTotalPrice =@NewPrice WHERE Note_Number='" + NoteNumber + "' AND Product_ID='" + Convert.ToInt32(row.ItemArray[1]) + "'";
                MySqlCommand cmdU = new MySqlCommand(AvePrice, mysqlCon);
                cmdU.Parameters.Add("@NewPrice", MySqlDbType.Double, 50).Value = NewPrice.ToString("N2");
                cmdU.ExecuteNonQuery();
            }



            string sqlUpdateProduct = "UPDATE receive_notes SET Status ='Затворена' WHERE Note_Number='" + NoteNumber + "'";
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.ExecuteNonQuery();
            
            mysqlCon.Close();

            
        }

        public void CloseImportReceiveNote(int NoteNumber)
        {
            DataSet table = new DataSet();
            DataSet Notetable = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT * from receive_note_products WHERE Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);

            string sqlSelectNote = "SELECT * from receive_notes WHERE Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectNote, mysqlCon);
            MyDA.Fill(Notetable);

            Decimal TotalPrice=0;
            Decimal NewPrice = 0;
            foreach (DataRow prod in table.Tables[0].Rows)
            {
                TotalPrice+=Convert.ToDecimal(prod.ItemArray[2])*Convert.ToDecimal(prod.ItemArray[6]);
            }

            foreach (DataRow row in table.Tables[0].Rows)
            {
                Int32 OldQuantity = GetProductQuantityFromWarehouse(Convert.ToInt32(row.ItemArray[1]));
                Int32 NewQuantity = Convert.ToInt32(row.ItemArray[2]);
                Decimal OldPrice = GetAvgPriceFromWarehouse(Convert.ToInt32(row.ItemArray[1]));
                Decimal Share=Convert.ToDecimal(row.ItemArray[6])/TotalPrice;
                if (Notetable.Tables[0].Rows[0].ItemArray[10].ToString() == "MKD")
                {
                    Decimal Price = Convert.ToDecimal(row.ItemArray[6]);
                    Decimal Transport = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[7].ToString());
                    Decimal Customs = Convert.ToDecimal(row.ItemArray[8]);
                    Decimal Spedition = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[8].ToString());
                    Decimal Warehousing = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[9].ToString());
                    NewPrice = ((Price + Transport * Share) * (1 + Customs / 100)) + (Spedition+Warehousing) * Share;
                }
                else
                {
                    Decimal PriceInValute=Convert.ToDecimal(row.ItemArray[6]);
                    Decimal Curse = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[11].ToString());
                    Decimal Transport = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[7].ToString());
                    Decimal Customs=Convert.ToDecimal(row.ItemArray[8]);
                    Decimal Spedition = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[8].ToString());
                    Decimal Warehousing = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[9].ToString());
                    NewPrice = ((PriceInValute * Curse + Transport * Share) * (1 + Customs / 100)) + Spedition * Share + Warehousing * Share;
                }
                UpdateWarehouseQuantity(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), NewQuantity);
                if (OldPrice == 0)
                {
                    UpdateWarehouseAvgPrice(Convert.ToInt32(row.ItemArray[1]), NewPrice);
                }
                else
                {
                    UpdateWarehouseAvgPrice(Convert.ToInt32(row.ItemArray[1]), (OldPrice * OldQuantity + NewPrice * NewQuantity) / (OldQuantity + NewQuantity));
                }
                string AvePrice = "UPDATE receive_note_products SET AveTotalPrice =@NewPrice WHERE Note_Number='" + NoteNumber + "' AND Product_ID='" + Convert.ToInt32(row.ItemArray[1]) + "'";
                MySqlCommand cmdU = new MySqlCommand(AvePrice, mysqlCon);
                cmdU.Parameters.Add("@NewPrice", MySqlDbType.Double, 50).Value = NewPrice;
                cmdU.ExecuteNonQuery();
                
            }



            string sqlUpdateProduct = "UPDATE receive_notes SET Status ='Затворена' WHERE Note_Number='" + NoteNumber + "'";
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();


        }

        public void CalculateImportReceiveNote(int NoteNumber)
        {
            DataSet table = new DataSet();
            DataSet Notetable = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT * from receive_note_products WHERE Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);

            string sqlSelectNote = "SELECT * from receive_notes WHERE Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectNote, mysqlCon);
            MyDA.Fill(Notetable);

            Decimal TotalPrice = 0;
            Decimal NewPrice = 0;
            foreach (DataRow prod in table.Tables[0].Rows)
            {
                TotalPrice += Convert.ToDecimal(prod.ItemArray[2]) * Convert.ToDecimal(prod.ItemArray[6]);
            }

            foreach (DataRow row in table.Tables[0].Rows)
            {
                Int32 OldQuantity = GetProductQuantityFromWarehouse(Convert.ToInt32(row.ItemArray[1]));
                Int32 NewQuantity = Convert.ToInt32(row.ItemArray[2]);
                Decimal OldPrice = GetAvgPriceFromWarehouse(Convert.ToInt32(row.ItemArray[1]));
                Decimal Share = Convert.ToDecimal(row.ItemArray[6]) / TotalPrice;
                if (Notetable.Tables[0].Rows[0].ItemArray[10].ToString() == "MKD")
                {
                    Decimal Price = Convert.ToDecimal(row.ItemArray[6]);
                    Decimal Transport = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[7].ToString());
                    Decimal Customs = Convert.ToDecimal(row.ItemArray[8]);
                    Decimal Spedition = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[8].ToString());
                    Decimal Warehousing = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[9].ToString());
                    NewPrice = ((Price + Transport * Share) * (1 + Customs / 100)) + (Spedition + Warehousing) * Share;
                }
                else
                {
                    Decimal PriceInValute = Convert.ToDecimal(row.ItemArray[6]);
                    Decimal Curse = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[11].ToString());
                    Decimal Transport = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[7].ToString());
                    Decimal Customs = Convert.ToDecimal(row.ItemArray[8]);
                    Decimal Spedition = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[8].ToString());
                    Decimal Warehousing = Convert.ToDecimal(Notetable.Tables[0].Rows[0].ItemArray[9].ToString());
                    NewPrice = ((PriceInValute * Curse + Transport * Share) * (1 + Customs / 100)) + Spedition * Share + Warehousing * Share;
                }
                
                string AvePrice = "UPDATE receive_note_products SET AveTotalPrice =@NewPrice WHERE Note_Number='" + NoteNumber + "' AND Product_ID='" + Convert.ToInt32(row.ItemArray[1]) + "'";
                MySqlCommand cmdU = new MySqlCommand(AvePrice, mysqlCon);
                cmdU.Parameters.Add("@NewPrice", MySqlDbType.Double, 50).Value = NewPrice;
                cmdU.ExecuteNonQuery();

            }
            mysqlCon.Close();


        }

        public DataSet GetAllWorkOrders(string ProjectID)
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            DataSet table = new DataSet();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();
           // "SELECT warehouse.*,products.* FROM warehouse LEFT JOIN products ON warehouse.Product_ID=products.ID WHERE warehouse.Product_ID = '" + ProductID + "'";

            string sqlSelectAll = "SELECT work_orders.*,projects.Name FROM work_orders  LEFT JOIN projects ON work_orders.ProjectID=projects.ID WHERE ProjectID=" + ProjectID;
           // MessageBox.Show(sqlSelectAll);

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            MyDA.Fill(table);

            mysqlCon.Close();

            return table;
        }

        public DataSet GetAllWorkOrders()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            DataSet table = new DataSet();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT work_orders.*,projects.Name FROM work_orders  LEFT JOIN projects ON work_orders.ProjectID=projects.ID";
            // MessageBox.Show(sqlSelectAll);

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            MyDA.Fill(table);

            mysqlCon.Close();

            return table;
        }

        public void AddWordOrder(Int32 WorkOrderNo, string Responsible, DateTime Date, string Project, string Status, string Location, DateTime FinishDate, string Asignment, string Worker)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlInsert = "INSERT INTO work_orders VALUES (DEFAULT,'" +
                                        Date.ToString("yyyy-MM-dd") + "','" +
                                        Responsible + "','" +
                                        WorkOrderNo + "','" +
                                        Project + "','" +
                                        Status + "','" +
                                        Location + "','" +
                                        FinishDate.ToString("yyyy-MM-dd") + "','" +
                                        Asignment + "','" +
                                        Worker + "')";
            MySqlCommand cmd = new MySqlCommand(sqlInsert, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();
        }

        public Int32 GetLastWorkOrder()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT MAX(Note_Number) FROM work_orders";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();
            try
            {
                return Convert.ToInt32(result.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return 0;
            }
        }

        public void DeleteAllProductsFromWorkOrderList(int ReceiveNoteNumber)
        {
            DataSet table = new DataSet();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sql = "DELETE FROM work_order_products WHERE Note_Number='" + ReceiveNoteNumber + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public bool CloseWorkOrder(int NoteNumber)
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT work_order_products.*,warehouse.Averige_Price,warehouse.Quantity from work_order_products LEFT JOIN warehouse ON warehouse.Product_ID=work_order_products.ProductID AND warehouse.Warehouse=work_order_products.Warehouse WHERE Note_Number='" + NoteNumber + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);

            foreach (DataRow row in table.Tables[0].Rows)
            {
                try
                {
                    if (Convert.ToInt32(row.ItemArray[2]) > Convert.ToInt32(row.ItemArray[8]))
                    {
                        MessageBox.Show("Проверете го налогот\nИстиот содржи производи кои ги нема доволно во магацин");
                        return false;
                    }
                }
                catch
                {
                    MessageBox.Show("Проверете го налогот\nИстиот содржи производи кои ги нема доволно во магацин");
                    return false;
                }
            }

            foreach (DataRow row in table.Tables[0].Rows)
            {
                UpdateWarehouseQuantity(Convert.ToInt32(row.ItemArray[1]), row.ItemArray[5].ToString(), (-Convert.ToInt32(row.ItemArray[2])));
                string sqlUpdatePrice = "UPDATE work_order_products SET Price =@Price WHERE ID='" + Convert.ToInt32(row.ItemArray[0]) + "'";
                MySqlCommand cmdU = new MySqlCommand(sqlUpdatePrice, mysqlCon);
                cmdU.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = row.ItemArray[7];
                cmdU.ExecuteNonQuery();
            }

            string sqlUpdateProduct = "UPDATE work_orders SET Status ='Затворен' WHERE Note_Number='" + NoteNumber + "'";
            // MessageBox.Show(sqlUpdateProduct);
            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
            return true;
        }

        public DataTable GetProductFromWarehouse(Int32 ProductID)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT * FROM warehouse " +
                                         "WHERE Product_ID = '" + ProductID + "'";

            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];
            
        }

        public DataTable GetJoinProductFromWarehouse(Int32 ProductID)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT warehouse.*,products.* FROM warehouse LEFT JOIN products ON warehouse.Product_ID=products.ID WHERE warehouse.Product_ID = '" + ProductID + "'";
            MessageBox.Show(sql);
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];

        }

        public DataTable GetAllJoinProductFromWarehouse()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT products.Part_Num,products.Name,products.Description,SUM(warehouse.Quantity),warehouse.Barcode,warehouse.Averige_Price,products.Measure,products.ID,products.Min_quantity,products.Category_1,products.Category_2,products.Category_3 FROM warehouse LEFT JOIN products ON warehouse.Product_ID=products.ID GROUP BY warehouse.Product_ID";
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];

        }

        public DataTable GetAllJoinProductFromWarehouse(string Warehouse)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT products.Part_Num,products.Name,products.Description,warehouse.Quantity,warehouse.Barcode,warehouse.Averige_Price,products.Measure,products.ID,products.Min_quantity,products.Category_1,products.Category_2,products.Category_3 FROM warehouse LEFT JOIN products ON warehouse.Product_ID=products.ID WHERE warehouse.Warehouse='" + Warehouse + "'";
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];

        }

        public void UpdateWorkOrder(DateTime Date, string Responsible, string Location, DateTime FinishDate, string Asigment, string Worker, string NoteNumber)
        {

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlUpdateProduct = "UPDATE work_orders SET " +
                                      "Date ='" + Date.ToString("yyyy-MM-dd") + "'," +
                                      "Responsible ='" + Responsible + "', " +
                                      "Location ='" + Location + "', " +
                                      "FinishDate ='" + FinishDate.ToString("yyyy-MM-dd") + "', " +
                                      "Asigment ='" + Asigment + "', " +
                                      "Worker = '"+ Worker+"' WHERE Note_Number='"+NoteNumber +"'";


            MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
            cmd.ExecuteNonQuery();

            mysqlCon.Close();

        }

        public void DeleteWorkOrder(int ReceiveNoteNumber)
        {
            DataSet table = new DataSet();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sql = "DELETE FROM work_orders WHERE Note_Number='" + ReceiveNoteNumber + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            sql = "DELETE FROM work_order_products WHERE Note_Number='" + ReceiveNoteNumber + "'";
            cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public DataTable GetProductCardReceiveNotes()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT receive_notes.Note_Number,receive_notes.Date,receive_notes.Partner,products.Part_Num,products.Name,products.Description,receive_note_products.Quantity,products.Measure,receive_note_products.Price, products.Category_1,products.Category_2,products.Category_3,receive_note_products.AveTotalPrice FROM receive_note_products LEFT JOIN receive_notes ON receive_notes.Note_Number=receive_note_products.Note_Number LEFT JOIN products ON products.ID=receive_note_products.Product_ID";
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];

        }

        public DataTable GetProductCardDispatchNotes()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT dispatch_note.Note_Number,dispatch_note.Date,dispatch_note.Partner,products.Part_Num,products.Name,products.Description,dispatch_note_products.Quantity,products.Measure,dispatch_note_products.Price, products.Category_1,products.Category_2,products.Category_3 FROM dispatch_note_products LEFT JOIN dispatch_note ON dispatch_note.Note_Number=dispatch_note_products.Note_Number LEFT JOIN products ON products.ID=dispatch_note_products.ProductID";
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];

        }

        public DataTable GetProductCardWorkOrder()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sql = "SELECT work_orders.Note_Number,work_orders.Date,projects.Partner,products.Part_Num,products.Name,products.Description,work_order_products.Quantity,products.Measure,work_order_products.Price, products.Category_1,products.Category_2,products.Category_3 FROM work_order_products LEFT JOIN work_orders ON work_orders.Note_Number=work_order_products.Note_Number LEFT JOIN products ON products.ID=work_order_products.ProductID LEFT JOIN projects ON projects.ID=work_orders.ProjectID";
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table.Tables[0];

        }

        public void CorectWorkOrderProductPrices()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT work_order_products.*,warehouse.Averige_Price from work_order_products LEFT JOIN warehouse ON warehouse.Product_ID=work_order_products.ProductID";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);


            foreach (DataRow row in table.Tables[0].Rows)
            {
                string sqlUpdatePrice = "UPDATE work_order_products SET Price =@Price WHERE ID='" + Convert.ToInt32(row.ItemArray[0]) + "'";
                MySqlCommand cmdU = new MySqlCommand(sqlUpdatePrice, mysqlCon);
                cmdU.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = row.ItemArray[7];
                cmdU.ExecuteNonQuery();
            }
            mysqlCon.Close();
        }

        public void CorectReceaveNotePrices()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT receive_note_products.*,warehouse.Averige_Price from receive_note_products LEFT JOIN warehouse ON warehouse.Product_ID=receive_note_products.Product_ID";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);


            foreach (DataRow row in table.Tables[0].Rows)
            {
                string sqlUpdatePrice = "UPDATE receive_note_products SET AveTotalPrice =@Price WHERE ID='" + Convert.ToInt32(row.ItemArray[0]) + "'";
                MySqlCommand cmdU = new MySqlCommand(sqlUpdatePrice, mysqlCon);
                cmdU.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = row.ItemArray[10];
                cmdU.ExecuteNonQuery();
            }
            mysqlCon.Close();
        }
    }
}
